﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using OgilvyOne.IDAL;
using OgilvyOne.Model;


namespace OgilvyOne.SqlServerDAL
{
    public class MediaContact:IMediaContact
    {
        string connstr = SQLHelper.CONN_STRING_NON_DTC;

        /// </summary>
        /// <param> 获取媒体信息</param>
        /// <returns></returns>
        public IList getContactus(MediaContactModel cum)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select MEDIA_CONTACT_ID,MEDIA_NAME,CONTACT_UNIT,CONTACT_TEL,CONTACT_USERNAME,STATE,ADD_TIME,USERNAME from COFCO_MEDIA_CONTACT a left join CMS_USERS b on a.ADD_USERID = b.AID ");
            if (cum.MediaName!=null)
            {
                strSql.Append(" where MEDIA_NAME = '"+cum.MediaName.ToString()+"'");
            }
            strSql.Append(" order by ADD_TIME desc ");
            SqlParameter[] parms = new SqlParameter[] {
            };
            IList datalist = new ArrayList();
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, strSql.ToString(), parms))
            {
                try
                {
                    while (rdr.Read())
                    {
                        MediaContactModel mcm = new MediaContactModel();
                        mcm.MediaContacId = rdr.GetInt32(0);
                        if (!rdr.IsDBNull(1)) mcm.MediaName = rdr.GetString(1);
                        if (!rdr.IsDBNull(2)) mcm.ContactUnit = rdr.GetString(2);
                        if (!rdr.IsDBNull(3)) mcm.ContactTel = rdr.GetString(3);
                        if (!rdr.IsDBNull(4)) mcm.ContactUserName = rdr.GetString(4);
                        if (!rdr.IsDBNull(5)) mcm.State = rdr.GetInt32(5);
                        if (!rdr.IsDBNull(6)) mcm.AddTime = rdr.GetDateTime(6);
                        if (!rdr.IsDBNull(7)) mcm.UserName = rdr.GetString(7);

                        datalist.Add(mcm);
                    }
                }
                catch (Exception ex)
                { }
            }
            return datalist;
        }

        /// </summary>
        /// <param> 删除媒体信息</param>
        /// <returns></returns>
        public bool delete(int mcid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from COFCO_MEDIA_CONTACT where MEDIA_CONTACT_ID = @MEDIA_CONTACT_ID");
            SqlParameter[] parameters = {
					new SqlParameter("@MEDIA_CONTACT_ID", SqlDbType.Int,4)
             };
            parameters[0].Value = mcid;
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), parameters) > 0;
        }
        /// </summary>
        /// <param> 添加媒体信息</param>
        /// <returns></returns>
        public int Add(MediaContactModel cum)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into COFCO_MEDIA_CONTACT (MEDIA_NAME,MEDIA_IMG,CONTACT_UNIT,CONTACT_TEL,CONTACT_FAX,CONTACT_EMAIL,CONTACT_USERNAME,CONTACT_MOBLIE,STATE,ADD_TIME,ADD_USERID)");
            strSql.Append(" values (@MEDIA_NAME,@MEDIA_IMG,@CONTACT_UNIT,@CONTACT_TEL,@CONTACT_FAX,@CONTACT_EMAIL,@CONTACT_USERNAME,@CONTACT_MOBLIE,@STATE,getdate(),@ADD_USERID)");
            SqlParameter[] parameters = {
                    new SqlParameter("@MEDIA_NAME",SqlDbType.VarChar,100),
                    new SqlParameter("@MEDIA_IMG",SqlDbType.VarChar,50),
                    new SqlParameter("@CONTACT_UNIT",SqlDbType.VarChar,200),
                    new SqlParameter("@CONTACT_TEL",SqlDbType.VarChar,20),
                    new SqlParameter("@CONTACT_FAX",SqlDbType.VarChar,20),
                    new SqlParameter("@CONTACT_EMAIL",SqlDbType.VarChar,200),
                    new SqlParameter("@CONTACT_USERNAME",SqlDbType.VarChar,50),
                    new SqlParameter("@CONTACT_MOBLIE",SqlDbType.VarChar,20),
                    new SqlParameter("@STATE",SqlDbType.Int),
                    new SqlParameter("@ADD_USERID",SqlDbType.Int),
                                        };
            parameters[0].Value = cum.MediaName;
            parameters[1].Value=cum.MediaImg;
            parameters[2].Value=cum.ContactUnit;
            parameters[3].Value=cum.ContactTel;
            parameters[4].Value=cum.ContactFax;
            parameters[5].Value=cum.ContactEmail;
            parameters[6].Value=cum.ContactUserName;
            parameters[7].Value=cum.ContactMoblie;
            parameters[8].Value=cum.State;
            parameters[9].Value=cum.AddUserId;
            
            int ts = 1;
            return ts = SQLHelper.ExecuteSql(strSql.ToString(), parameters);
        }
        /// </summary>
        /// <param> 获取媒体信息</param>
        /// <returns></returns>
        public MediaContactModel Get(int mcid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.*,USERNAME from COFCO_MEDIA_CONTACT a left join CMS_USERS b on a.ADD_USERID = b.AID ");
            strSql.Append("where MEDIA_CONTACT_ID = @MediaContacId");
            MediaContactModel mcm = new MediaContactModel();
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@MediaContacId", SqlDbType.Int)
            };
            parms[0].Value = mcid;

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, strSql.ToString(), parms))
            {
                if (rdr.Read())
                {
                    mcm.MediaContacId=rdr.GetInt32(0);
                    if (!rdr.IsDBNull(1)) mcm.MediaName=rdr.GetString(1);
                    if (!rdr.IsDBNull(2)) mcm.MediaImg=rdr.GetString(2);
                    if (!rdr.IsDBNull(3)) mcm.ContactUnit=rdr.GetString(3);
                    if (!rdr.IsDBNull(4)) mcm.ContactTel=rdr.GetString(4);
                    if (!rdr.IsDBNull(5)) mcm.ContactFax=rdr.GetString(5);
                    if (!rdr.IsDBNull(6)) mcm.ContactEmail=rdr.GetString(6);
                    if (!rdr.IsDBNull(7)) mcm.ContactUserName=rdr.GetString(7);
                    if (!rdr.IsDBNull(8)) mcm.ContactMoblie=rdr.GetString(8);
                    if (!rdr.IsDBNull(9)) mcm.State=rdr.GetInt32(9);
                    if (!rdr.IsDBNull(10)) mcm.AddTime = rdr.GetDateTime(10);
                    if (!rdr.IsDBNull(11)) mcm.AddUserId = rdr.GetInt32(11);
                    if (!rdr.IsDBNull(12)) mcm.UserName = rdr.GetString(12);

                }
            }
            return mcm;
        }
        /// </summary>
        /// <param> 更新媒体信息</param>
        /// <returns></returns>
        public int Update(MediaContactModel cum)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE COFCO_MEDIA_CONTACT SET MEDIA_NAME = @MEDIA_NAME , MEDIA_IMG = @MEDIA_IMG , CONTACT_UNIT = @CONTACT_UNIT , CONTACT_TEL = @CONTACT_TEL , CONTACT_FAX = @CONTACT_FAX,");
            strSql.Append("CONTACT_EMAIL = @CONTACT_EMAIL,CONTACT_USERNAME = @CONTACT_USERNAME,CONTACT_MOBLIE = @CONTACT_MOBLIE,STATE = @STATE WHERE MEDIA_CONTACT_ID = @MEDIA_CONTACT_ID");
            SqlParameter[] parameters = {
                    new SqlParameter("@MEDIA_NAME",SqlDbType.VarChar,100),
                    new SqlParameter("@MEDIA_IMG",SqlDbType.VarChar,50),
                    new SqlParameter("@CONTACT_UNIT",SqlDbType.VarChar,200),
                    new SqlParameter("@CONTACT_TEL",SqlDbType.VarChar,20),
                    new SqlParameter("@CONTACT_FAX",SqlDbType.VarChar,20),
                    new SqlParameter("@CONTACT_EMAIL",SqlDbType.VarChar,200),
                    new SqlParameter("@CONTACT_USERNAME",SqlDbType.VarChar,50),
                    new SqlParameter("@CONTACT_MOBLIE",SqlDbType.VarChar,20),
                    new SqlParameter("@STATE",SqlDbType.Int),
                    new SqlParameter("@MEDIA_CONTACT_ID",SqlDbType.Int),
                                        };
            parameters[0].Value = cum.MediaName;
            parameters[1].Value = cum.MediaImg;
            parameters[2].Value = cum.ContactUnit;
            parameters[3].Value = cum.ContactTel;
            parameters[4].Value = cum.ContactFax;
            parameters[5].Value = cum.ContactEmail;
            parameters[6].Value = cum.ContactUserName;
            parameters[7].Value = cum.ContactMoblie;
            parameters[8].Value = cum.State;
            parameters[9].Value = cum.MediaContacId;

            int ts = 1;
            return ts = SQLHelper.ExecuteSql(strSql.ToString(), parameters);
        }
    }
}
